// 引入执行SQL命令方法
const { query } = require("../db/pool.js");
// 引入验证token方法
const { verify } = require("../utils/auth.js");
// 引入express模块
const express = require("express");
// 创建路由器对象
const router = express.Router();
// 添加路由（编写接口）
// 1.各类缴费金额列表接口（get /list）
// 接口地址  /fee/list
router.get("/list", verify, async function (req, res, next) {
  // 获取用户的id（get传参）
  // var obj = req.query
  // console.log(obj)
  try {
    // 查询当前用户各类缴费总额
    // 从登录成功后的token中获取用户的id
    var result = await query(
      "select type,sum(amount) as n from zh_fee where userId=? group by type",
      [req.token.uid]
    );
  } catch (err) {
    return next(err);
  }
  res.send({
    shui: result[0].n,
    dian: result[1].n,
    ranqi: result[2].n,
    wuye: result[3].n,
    tingche: result[4].n,
    kuandai: result[5].n,
  });
});
// 2.缴费单位列表（get /collector）
// /fee/collector
router.get("/collector", verify, async function (req, res, next) {
  // 获取get传递的参数
  var obj = req.query;
  console.log(obj);
  // 执行SQL命令，查询对应的缴费单位
  try {
    var result = await query(
      "select cid,cname from zh_fee_collector where type=?",
      [obj.type]
    );
  } catch (err) {
    return next(err);
  }
  res.send(result);
});
// 3.业主信息(get  /houseHold)
// /fee/houseHold
router.get("/houseHold", verify, async function (req, res, next) {
  // 获取get传递的参数
  var obj = req.query;
  console.log(obj);
  // 执行SQL命令
  try {
    var result = await query(
      "select hid,householdName,householdAddr from zh_household where householdId=?",
      obj.hhid
    );
  } catch (err) {
    return next(err);
  }
	console.log(result);
	
  // 判断是否查询到业主
  if (result.length === 0) {
    res.send({ code: 400, msg: "can not find" });
  } else {
    // 修改返回的业主信息
    result[0].householdName = result[0].householdName.replace(
      /(.)(.)(.?)/,
      '$1*$3'
    );
    result[0].householdAddr = result[0].householdAddr.replace(/\d{4}$/, '****');
    res.send({ code: 200, msg: "find", rows: result });
  }
});

// 4.添加缴费(post /add)
// /fee/add
router.post("/add", verify, async function (req, res, next) {
  // 获取post传递的参数
  var obj = req.body;
  console.log(obj);
  // 验证各项是否为空
  if (!obj.type) {
    return res.send({ code: 401, msg: "type required" });
  }
  if (!obj.collectorId) {
    return res.send({ code: 402, msg: "collectorId requried" });
  }
  if (!obj.householdId) {
    return res.send({ code: 403, msg: "householdId required" });
  }
  if (!obj.amount) {
    return res.send({ code: 404, msg: "amount required" });
  }
  console.log("id:", req.token.uid);
  // 执行SQL命令
  try {
    var result = await query("insert into zh_fee values(null,?,?,?,?,?,0,?)", [
      req.token.uid,
      obj.type,
      obj.collectorId,
      obj.householdId,
      Date.now(),
      obj.amount,
    ]);
  } catch (err) {
    return next(err);
  }
  console.log(result);
  res.send({ code: 200, msg: "add fee success" });
});
// 5.缴费记录列表(get /record)
// /fee/record
router.get('/record',verify,async function(req,res,next) {
	// 获取get传递的参数
	var obj = req.query
	console.log(obj)
	// 编写SQL命令，查询出当前用户所有的缴费记录
  var sql = 'select a.fid,a.type,a.userId,a.amount,a.householdId,a.generatedTime,b.cname,c.householdName from '
    + 'zh_fee as a, zh_fee_collector as b, zh_household as c ' +
    'where a.collectorId = b.cid and a.householdId = c.householdId and a.userId =? and a.generatedTime > 0'
	// 准备数组，用于过滤传递的值，然后拼接进去
	var params = [req.token.uid]
	// 如果存在缴费类型
  if (obj.type && obj.type > 0) {
    // 将缴费类型拼接到SQL命令
    sql += ' and a.type=?'
    // 将获取的类型就行过滤
    params.push(obj.type)
  }
	// 如果存在查询时间，并且结束时间大于开始时间，拼接
	if(obj.startTime && obj.endTime && obj.endTime>obj.startTime) {
		sql += ' and generatedTime>=? and generatedTime<=?'
		params.push(obj.startTime,obj.endTime)
	}
	// 按照时间降序排列
	sql += ' order by generatedTime desc'
	// 执行SQL命令
	try {
		var result = await query(sql,params)
	} catch(err) {
		return next(err)
	}
	console.log(result)
	
	res.send(result)
})



// 暴露路由器对象
module.exports = router;
